1. Field
The present invention relates to databases, and more specifically to software, systems, and methods for improving the editing of databases.
2. Background
Databases are used to manipulate, store and report data. There are several different types of database structures, including flat databases and relational databases. A flat database has data organized in a single, two-dimensional array of data elements called a table. The Sports Team Table 110 of FIG. 1, taken by itself without 120 or 130, may be thought of as an example of a flat file table. Tables are organized in columns and rows. Each column of a table typically contains data elements of a similar data-type or value. For example, Sports Team Table 110 includes a Team ID column 111, a Team Name column 113, and a column 115 for the number of members per team. The data elements in each of the various rows may not be of similar types of values but are generally related to one another in some manner. Row 117 of table 110 contains data elements pertaining to the Girl's Swimming team.
FIG. 1 is a relational database 100 which includes tables 110, 120 and 130. Relational databases tend to be much more robust and versatile than flat databases. Relational databases store data in two or more interrelated tables in accordance with a schema defining the various interrelationships between the multiple tables of the relational database. For example, the relational database depicted in FIG. 1 includes table 110 with information about sports teams, table 120 with information about the members of a particular team, and table 130 with information about the events for a particular member of a team. The tables in a relational database may be interrelated in parent-child relationships. Table 110 is a parent of table 120. Table 120 is a child of table 110, but is a parent of table 130.
Relational databases generally have two main categories of instructions, Data Manipulation Language (DML) instructions and Data Definition Language (DDL) instructions. The DML instructions are used for manipulating, adding or deleting the data stored in relational database. DML instructions do not affect the database structure itself Some of the most common DML commands include the SELECT, INSERT, UPDATE and DELETE commands for respectively extracting, adding, modifying and deleting data. The DDL commands, on the other hand, are used to alter the database objects containing data—that is, the database structure. The DDL commands do not directly affect the data. The database objects affected by DDL commands include the tables, indexes and relationships of the database structure, but not the data itself.
A single DML command executed in a large relational database may iterate through many thousands of records, often placing great demands on the system's computational resources. When thousands of records are inserted, updated or deleted by a conventional application, the transaction log file may become filled up, causing the statement to fail. When the CASCADE DELETE rule is specified the deletion of a record from a parent table cascades to the children of the parent, so the problem tends to be worse when trying to delete records from a parent table with many children records. For an application to handle a DML command affecting thousands of records, special steps must sometimes be taken to manage the INSERT, DELETE or UPDATE statement to all of the tables involved. Programmers working with conventional database applications have found a work-around for this problem. Programmers can avoid the problems which occur when the transaction log file fills up by drafting customized SQL code to retrieve the primary key values for the records to be updated or deleted, and storing them in the application's memory. The custom SQL code can then loop through the memory issuing the update or delete statement on the data records, one record at a time, and performs commit after every N records. In this way, the programmer's custom SQL code can avoid having the transaction log fill up. However, this work-around is somewhat inefficient inasmuch as it requires the use of customized code to prevent the transaction log overflow problem. What is needed is an improved way of executing DML commands in large relational databases to avoid straining the computational resources of the computer system.